package com.weixing.mall.provider.service.impl;

import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.weixing.mall.base.constant.GlobalConstant;
import com.weixing.mall.core.enums.OrderSysTagEnum;
import com.weixing.mall.provider.api.model.enums.AgentLevelEnum;
import com.weixing.mall.provider.api.model.enums.AgentTypeEnum;
import com.weixing.mall.provider.api.model.enums.OrderTypeEnum;
import com.weixing.mall.provider.mapper.DayReportFinanceMapper;
import com.weixing.mall.provider.model.domain.Order;
import com.weixing.mall.provider.model.enums.*;
import com.weixing.mall.provider.model.vo.report.finance.*;
import com.weixing.mall.provider.model.vo.report.finance.newest.regular.*;
import com.weixing.mall.provider.model.vo.report.finance.newest.seckill.FinanceSeckillSummary1Vo;
import com.weixing.mall.provider.model.vo.report.finance.newest.seckill.FinanceSeckillSummary2Vo;
import com.weixing.mall.provider.model.vo.report.finance.newest.seckill.FinanceSeckillSummaryDetail1Vo;
import com.weixing.mall.provider.model.vo.report.finance.newest.seckill.FinanceSeckillSummaryRebateDetailVo;
import com.weixing.mall.provider.service.IDayReportFinanceService;
import io.swagger.models.auth.In;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;
import sun.management.resources.agent;

/**
 * @author jzy
 * @date 2020/5/30
 */
@Service
public class DayReportFinanceServiceImpl extends ServiceImpl<DayReportFinanceMapper, Order> implements IDayReportFinanceService {
    @Override
    public IPage<FinanceRegularSummary1Vo> regularSummary1(Integer orderType,String yearMonth, String startTime, String endTime, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("count(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+",true,null)) as quantity" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+",o.order_price,0)) as amount" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+",goods.cost_price,0)) as amountCost" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+",o.order_price*agent.agent_discount,0)) as actualPaidAmount" +
                ",count(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +",true,null)) as returnQuantity" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +",goods.cost_price,0)) as returnAmountCost" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +",o.order_price*agent.agent_discount,0)) as actualReturnAmount" +
                ",count(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and (o.sys_tag="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" or o.sys_tag="+ OrderSysTagEnum.QXK_TAG_401.getValue() +"),true,null)) as stockoutQuantity" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and (o.sys_tag="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" or o.sys_tag="+ OrderSysTagEnum.QXK_TAG_401.getValue() +"),goods.cost_price,0)) as stockoutAmountCost" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and (o.sys_tag="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" or o.sys_tag="+ OrderSysTagEnum.QXK_TAG_401.getValue() +"),o.order_price*agent.agent_discount,0)) as actualStockoutAmount" +
                ",count(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag!="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +",true,null)) as actualQuantity" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag!="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +",goods.cost_price,0)) as actualAmountCost" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag!="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +",o.order_price*agent.agent_discount,0)) as actualAmount" +
                ",sum(if(agent.profit_type="+ GlobalConstant.PROFIT_TYPE_BONUS +", agent.agent_profit,0)) as rebate" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and agent.order_from="+OrderFromEnum.MEMBER.getValue()+",o.order_price,0)) onlinePaid");
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        //订单审核时间、订单取消时间、订单断货时间、订单退货时间
        q.and(StrUtil.isNotEmpty(startTime),
                i->i.and(j->j.ge("date_format(o.audit_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime))
                        .or(z->z.ge("date_format(o.canceled_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(o.canceled_time,'%Y-%m-%d')",endTime))
                        .or(l->l.ge("date_format(o.nosupplly_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(o.nosupplly_time,'%Y-%m-%d')",endTime))
                        .or(m->m.ge("date_format(afterSale.audited_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",endTime)));
        q.and(StrUtil.isNotEmpty(yearMonth),i->i.eq("date_format(o.audit_time,'%Y-%m')",yearMonth)
                .or().eq("date_format(o.canceled_time,'%Y-%m-%d')",yearMonth)
                .or().eq("date_format(o.nosupplly_time,'%Y-%m-%d')",yearMonth)
                .or().eq("date_format(afterSale.audited_time,'%Y-%m-%d')",yearMonth));
        q.eq("agent.agent_pid",0);
        q.eq(orderType!=null,"o.order_type",orderType);
        return baseMapper.regularSummary1(initMpPage,q);
    }

    @Override
    public IPage<FinanceRegularSummary2Vo> regularSummary2(Integer roleType, Long agentId, Integer orderType,String yearMonth, String startTime, String endTime, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.customer_id customerId,o.customer_name customerName" +
                ",IF (abg.agent_id IS NULL,agent.agent_name,null) agentName" +
                ",IF(abg.agent_id IS NULL,1,0) isTop" +
                ",case " +
                "when (abg.agent_id IS NULL) then agent.agent_id " +
                "when (abg.agent_id IS NOT NULL AND abg.obligee_id1st!=0 AND abg.obligee_id2nd =0) then abg.obligee_id1st " +
                "when (abg.agent_id IS NOT NULL AND abg.obligee_id1st!=0 AND abg.obligee_id2nd !=0) then abg.obligee_id2nd " +
                "else 0 END topId" +
                ",IF ((abg.agent_id IS NOT NULL and abg.obligee_id1st!=0 AND abg.obligee_id2nd =0),agent.agent_name,abg.obligee1st) agentName1" +
                ",IF ((abg.agent_id IS NOT NULL and abg.obligee_id1st!=0 AND abg.obligee_id2nd =0),abg.obligee_id1st,null) obligeeId1" +
                ",IF ((abg.agent_id IS NOT NULL and obligee_id1st!=0 AND abg.obligee_id2nd !=0),agent.agent_name,NULL) agentName2" +
                ",IF ((abg.agent_id IS NOT NULL and obligee_id1st!=0 AND abg.obligee_id2nd !=0),abg.obligee_id2nd,NULL) obligeeId2" +
                ",count(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+",true,null)) as quantity" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+",o.order_price,0)) as amount" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+",goods.cost_price,0)) as amountCost" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+",o.order_price*agent.agent_discount,0)) as actualPaidAmount" +
                ",count(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +",true,null)) as returnQuantity" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +",goods.cost_price,0)) as returnAmountCost" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +",o.order_price*agent.agent_discount,0)) as actualReturnAmount" +
                ",count(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and (o.sys_tag="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" or o.sys_tag="+ OrderSysTagEnum.QXK_TAG_401.getValue() +"),true,null)) as stockoutQuantity" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and (o.sys_tag="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" or o.sys_tag="+ OrderSysTagEnum.QXK_TAG_401.getValue() +"),goods.cost_price,0)) as stockoutAmountCost" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and (o.sys_tag="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" or o.sys_tag="+ OrderSysTagEnum.QXK_TAG_401.getValue() +"),o.order_price*agent.agent_discount,0)) as actualStockoutAmount" +
                ",count(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag!="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +",true,null)) as actualQuantity" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag!="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +",goods.cost_price,0)) as actualAmountCost" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag!="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +",o.order_price*agent.agent_discount,0)) as actualAmount" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and agent.order_from="+OrderFromEnum.MEMBER.getValue()+",o.order_price,0)) onlinePaid" +
                ",(SELECT sum(oog.agent_profit) FROM omc_order_agent oog left join amc_agent_bonus_group aabg on aabg.id=oog.bonus_team_id WHERE oog.profit_type=3 and agent.agent_id=aabg.agent_id) as rebate" +
                ",IF(abg.agent_id IS NOT NULL and abg.obligee1st IS NOT NULL AND abg.obligee2nd IS NULL,(select sum(oog.agent_profit) FROM omc_order_agent oog WHERE oog.agent_id=obligeeId1 and oog.profit_type = 3),NULL) bonusOne" +
                ",IF(abg.agent_id IS NOT NULL and abg.obligee1st IS NOT NULL AND abg.obligee2nd IS NOT NULL,(select sum(oog.agent_profit) FROM omc_order_agent oog WHERE oog.agent_id=obligeeId1 and oog.profit_type = 3),NULL) bonusTwo" +
                ",IF(abg.agent_id IS NOT NULL and abg.obligee1st IS NOT NULL AND abg.obligee2nd IS NOT NULL,(select sum(oog.agent_profit) FROM omc_order_agent oog WHERE oog.agent_id=obligeeId2 and oog.profit_type = 3),NULL) bonusTwo2" +
                "");
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        //订单审核时间、订单取消时间、订单断货时间、订单退货时间
        q.and(StrUtil.isNotEmpty(startTime),
                i->i.and(j->j.ge("date_format(o.audit_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime))
                        .or(z->z.ge("date_format(o.canceled_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(o.canceled_time,'%Y-%m-%d')",endTime))
                        .or(l->l.ge("date_format(o.nosupplly_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(o.nosupplly_time,'%Y-%m-%d')",endTime))
                        .or(m->m.ge("date_format(afterSale.audited_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",endTime)));
        q.and(StrUtil.isNotEmpty(yearMonth),i->i.eq("date_format(o.audit_time,'%Y-%m')",yearMonth)
                .or().eq("date_format(o.canceled_time,'%Y-%m-%d')",yearMonth)
                .or().eq("date_format(o.nosupplly_time,'%Y-%m-%d')",yearMonth)
                .or().eq("date_format(afterSale.audited_time,'%Y-%m-%d')",yearMonth));
        q.eq(roleType!=null&&roleType==1,"agent.agent_pid",0);
        q.eq(roleType!=null&&roleType==1,"agent.kefu_id",agentId);
        q.eq(roleType!=null&&roleType==2,"agent.agent_pid",agentId);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.groupBy("agent.agent_id");
        q.orderByAsc("topId");
        q.orderByDesc("isTop");
        return baseMapper.regularSummary2(initMpPage,q);
    }

    @Override
    public IPage<FinanceRegularSummaryDetail1Vo> regularSummaryDetail1(Integer roleType, Long agentId,Integer orderType,Integer status, String yearMonth, String startTime, String endTime, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.customer_id customerId,o.customer_name customerName" +
                ",IF (abg.agent_id IS NULL,agent.agent_name,null) agentName" +
                ",IF(abg.agent_id IS NULL,1,0) isTop" +
                ",case " +
                "when (abg.agent_id IS NULL) then agent.agent_id " +
                "when (abg.agent_id IS NOT NULL AND abg.obligee_id1st!=0 AND abg.obligee_id2nd =0) then abg.obligee_id1st " +
                "when (abg.agent_id IS NOT NULL AND abg.obligee_id1st!=0 AND abg.obligee_id2nd !=0) then abg.obligee_id2nd " +
                "else 0 END topId" +
                ",IF ((abg.agent_id IS NOT NULL and abg.obligee_id1st!=0 AND abg.obligee_id2nd =0),agent.agent_name,abg.obligee1st) agentName1" +
                ",IF ((abg.agent_id IS NOT NULL and obligee_id1st!=0 AND abg.obligee_id2nd !=0),agent.agent_name,NULL) agentName2" +
                ",goods.quantity,goods.price amount,o.order_price*agent.agent_discount actualPaidAmount,goods.cost_price amountCost,goods.style_no,o.order_sn,pay.out_trade_no" +
                ",goods.size,goods.color,express.contact_phone,express.contact_user,o.created_time,o.audit_time");
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        //订单审核时间、订单取消时间、订单断货时间、订单退货时间
        q.and(StrUtil.isNotEmpty(startTime),
                i->i.and(j->j.ge("date_format(o.audit_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime))
                        .or(z->z.ge("date_format(o.canceled_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(o.canceled_time,'%Y-%m-%d')",endTime))
                        .or(l->l.ge("date_format(o.nosupplly_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(o.nosupplly_time,'%Y-%m-%d')",endTime))
                        .or(m->m.ge("date_format(afterSale.audited_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",endTime)));
        q.and(StrUtil.isNotEmpty(yearMonth),i->i.eq("date_format(o.audit_time,'%Y-%m')",yearMonth)
                .or().eq("date_format(o.canceled_time,'%Y-%m-%d')",yearMonth)
                .or().eq("date_format(o.nosupplly_time,'%Y-%m-%d')",yearMonth)
                .or().eq("date_format(afterSale.audited_time,'%Y-%m-%d')",yearMonth));
        q.eq(roleType!=null&&roleType==1,"agent.agent_pid",0);
        q.eq(roleType!=null&&roleType==1,"agent.kefu_id",agentId);
        q.eq(roleType!=null&&roleType==2,"agent.agent_pid",agentId);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.eq(status!=null&&status==1,"o.sys_tag",OrderSysTagEnum.YTHC_TAG_505.getValue());
        q.and(status!=null&&status==2,i->i.eq("o.sys_tag",OrderSysTagEnum.DHK_TAG_402.getValue()).or().eq("o.sys_tag",OrderSysTagEnum.QXK_TAG_401.getValue()));
        q.orderByAsc("topId");
        q.orderByDesc("isTop");
        return baseMapper.regularSummaryDetail1(initMpPage,q);
    }

    @Override
    public IPage<FinanceRegularSummaryDetail4Vo> regularSummaryDetail4(Integer roleType, Long agentId, Integer orderType, Integer status, String yearMonth, String startTime, String endTime, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.customer_id customerId,o.customer_name customerName" +
                ",IF (abg.agent_id IS NULL,agent.agent_name,null) agentName" +
                ",IF(abg.agent_id IS NULL,1,0) isTop" +
                ",case " +
                "when (abg.agent_id IS NULL) then agent.agent_id " +
                "when (abg.agent_id IS NOT NULL AND abg.obligee_id1st!=0 AND abg.obligee_id2nd =0) then abg.obligee_id1st " +
                "when (abg.agent_id IS NOT NULL AND abg.obligee_id1st!=0 AND abg.obligee_id2nd !=0) then abg.obligee_id2nd " +
                "else 0 END topId" +
                ",IF ((abg.agent_id IS NOT NULL and abg.obligee_id1st!=0 AND abg.obligee_id2nd =0),agent.agent_name,abg.obligee1st) agentName1" +
                ",IF ((abg.agent_id IS NOT NULL and abg.obligee_id1st!=0),abg.obligee_id1st,null) obligeeId1" +
                ",IF ((abg.agent_id IS NOT NULL and obligee_id1st!=0 AND abg.obligee_id2nd !=0),agent.agent_name,NULL) agentName2" +
                ",IF ((abg.agent_id IS NOT NULL and obligee_id1st!=0 AND abg.obligee_id2nd !=0),abg.obligee_id2nd,NULL) obligeeId2" +
                ",goods.quantity,goods.price amount,o.order_price*agent.agent_discount actualPaidAmount,goods.cost_price amountCost,goods.style_no,o.order_sn,pay.out_trade_no" +
                ",goods.size,goods.color,express.contact_phone,express.contact_user,o.created_time,o.audit_time" +
                ",(SELECT sum(oog.agent_profit) FROM omc_order_agent oog left join amc_agent_bonus_group aabg on aabg.id=oog.bonus_team_id WHERE oog.profit_type=3 and agent.agent_id=aabg.agent_id) as rebate" +
                ",IF(abg.agent_id IS NOT NULL and abg.obligee_id1st!=0 AND abg.obligee_id2nd =0,(select sum(oog.agent_profit) FROM omc_order_agent oog WHERE oog.order_id=o.id and oog.agent_id=obligeeId1 and oog.profit_type = 3),NULL) bonusOne" +
                ",IF(abg.agent_id IS NOT NULL and abg.obligee_id1st!=0 AND abg.obligee_id2nd =0,abg.obligee1st,NULL) bonusOneAgentName" +
                ",IF(abg.agent_id IS NOT NULL and abg.obligee_id1st!=0 AND abg.obligee_id2nd !=0,(select sum(oog.agent_profit) FROM omc_order_agent oog WHERE oog.order_id=o.id and oog.agent_id=obligeeId1 and oog.profit_type = 3),NULL) bonusTwo" +
                ",IF(abg.agent_id IS NOT NULL and abg.obligee_id1st!=0 AND abg.obligee_id2nd !=0,abg.obligee1st,NULL) bonusTwoAgentName" +
                ",IF(abg.agent_id IS NOT NULL and abg.obligee_id1st!=0 AND abg.obligee_id2nd !=0,(select sum(oog.agent_profit) FROM omc_order_agent oog WHERE oog.order_id=o.id and oog.agent_id=obligeeId2 and oog.profit_type = 3),NULL) bonusTwo2" +
                ",IF(abg.agent_id IS NOT NULL and abg.obligee_id1st!=0 AND abg.obligee_id2nd !=0,abg.obligee2nd,NULL) bonusTwo2AgentName" +
                "");
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        //订单审核时间、订单取消时间、订单断货时间、订单退货时间
        q.and(StrUtil.isNotEmpty(startTime),
                i->i.and(j->j.ge("date_format(o.audit_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime))
                        .or(z->z.ge("date_format(o.canceled_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(o.canceled_time,'%Y-%m-%d')",endTime))
                        .or(l->l.ge("date_format(o.nosupplly_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(o.nosupplly_time,'%Y-%m-%d')",endTime))
                        .or(m->m.ge("date_format(afterSale.audited_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",endTime)));
        q.and(StrUtil.isNotEmpty(yearMonth),i->i.eq("date_format(o.audit_time,'%Y-%m')",yearMonth)
                .or().eq("date_format(o.canceled_time,'%Y-%m-%d')",yearMonth)
                .or().eq("date_format(o.nosupplly_time,'%Y-%m-%d')",yearMonth)
                .or().eq("date_format(afterSale.audited_time,'%Y-%m-%d')",yearMonth));
        q.eq(roleType!=null&&roleType==1,"agent.agent_pid",0);
        q.eq(roleType!=null&&roleType==1,"agent.kefu_id",agentId);
        q.eq(roleType!=null&&roleType==2,"agent.agent_pid",agentId);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.eq(status!=null&&status==1,"o.sys_tag",OrderSysTagEnum.YTHC_TAG_505.getValue());
        q.and(status!=null&&status==2,i->i.eq("o.sys_tag",OrderSysTagEnum.DHK_TAG_402.getValue()).or().eq("o.sys_tag",OrderSysTagEnum.QXK_TAG_401.getValue()));
        q.eq("agent.profit_type",GlobalConstant.PROFIT_TYPE_BONUS);
        q.orderByAsc("topId");
        q.orderByDesc("isTop");
        return baseMapper.regularSummaryDetail4(initMpPage,q);
    }

    @Override
    public IPage<FinanceRegularSummaryRebateDetailVo> regularSummaryRebateDetailVo(Integer roleType, Long agentId,Integer orderType, String yearMonth, String startTime, String endTime, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("agent.kefu_id customerId,agent.kefu_name customerName,agent.agent_name,agent.agent_id" +
                ",sum(if(agent.bonus_type=1,agent.agent_profit,0)) bonusOne" +
                ",sum(if(agent.bonus_type=2,agent.agent_profit,0)) bonusTwo" +
                ",sum(agent.agent_profit) rebate");
//只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        q.eq(StrUtil.isNotEmpty(yearMonth),"date_format(o.audit_time,'%Y-%m')",yearMonth);
        q.eq(roleType!=null&&roleType==1,"agent.agent_pid",0);
        q.eq(roleType!=null&&roleType==1,"agent.kefu_id",agentId);
        q.and(roleType!=null&&roleType==2,i->i.eq("agent.agent_pid",agentId).or().eq("agent.agent_id",agentId));
        q.eq("agent.profit_type",3);
        q.groupBy("agent.agent_id");
        q.eq(orderType!=null,"agent.order_type",orderType);
        return baseMapper.regularSummaryRebateDetailVo(initMpPage,q);
    }

    @Override
    public IPage<FinanceSeckillSummary1Vo> seckillSummary1(String yearMonth, String startTime, String endTime, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("count(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+",true,null)) as quantity" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+",o.order_price,0)) as amount" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+",goods.cost_price,0)) as amountCost" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+",o.order_price*agent.agent_discount,0)) as actualPaidAmount" +
                ",count(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +",true,null)) as returnQuantity" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +",goods.cost_price,0)) as returnAmountCost" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +",o.order_price*agent.agent_discount,0)) as actualReturnAmount" +
                ",count(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and (o.sys_tag="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" or o.sys_tag="+ OrderSysTagEnum.QXK_TAG_401.getValue() +"),true,null)) as stockoutQuantity" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and (o.sys_tag="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" or o.sys_tag="+ OrderSysTagEnum.QXK_TAG_401.getValue() +"),goods.cost_price,0)) as stockoutAmountCost" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and (o.sys_tag="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" or o.sys_tag="+ OrderSysTagEnum.QXK_TAG_401.getValue() +"),o.order_price*agent.agent_discount,0)) as actualStockoutAmount" +
                ",count(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag!="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +",true,null)) as actualQuantity" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag!="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +",goods.cost_price,0)) as actualAmountCost" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag!="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +",o.order_price*agent.agent_discount,0)) as actualAmount" +
                ",sum(if(agent.profit_type!="+ GlobalConstant.PROFIT_TYPE_COMM +", agent.agent_profit,0)) as rebate" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and agent.order_from="+OrderFromEnum.MEMBER.getValue()+",o.order_price,0)) onlinePaid");
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        //订单审核时间、订单取消时间、订单断货时间、订单退货时间
        q.and(StrUtil.isNotEmpty(startTime),
                i->i.and(j->j.ge("date_format(o.audit_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime))
                        .or(z->z.ge("date_format(o.canceled_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(o.canceled_time,'%Y-%m-%d')",endTime))
                        .or(l->l.ge("date_format(o.nosupplly_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(o.nosupplly_time,'%Y-%m-%d')",endTime))
                        .or(m->m.ge("date_format(afterSale.audited_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",endTime)));
        q.and(StrUtil.isNotEmpty(yearMonth),i->i.eq("date_format(o.audit_time,'%Y-%m')",yearMonth)
                .or().eq("date_format(o.canceled_time,'%Y-%m-%d')",yearMonth)
                .or().eq("date_format(o.nosupplly_time,'%Y-%m-%d')",yearMonth)
                .or().eq("date_format(afterSale.audited_time,'%Y-%m-%d')",yearMonth));
        q.eq("agent.agent_pid",0);
        q.eq("o.order_type",OrderTypeEnum.SECKILL.getValue());
        return baseMapper.seckillSummary1(initMpPage,q);
    }

    @Override
    public IPage<FinanceSeckillSummary2Vo> seckillSummary2(Integer roleType, Long agentId, String yearMonth, String startTime, String endTime, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.customer_id customerId,o.customer_name customerName" +
                ",IF (abg.agent_id IS NULL,agent.agent_name,null) agentName" +
                ",IF(abg.agent_id IS NULL,1,0) isTop" +
                ",case " +
                "when (abg.agent_id IS NULL) then agent.agent_id " +
                "when (abg.agent_id IS NOT NULL AND abg.obligee_id1st!=0 AND abg.obligee_id2nd =0) then abg.obligee_id1st " +
                "when (abg.agent_id IS NOT NULL AND abg.obligee_id1st!=0 AND abg.obligee_id2nd !=0) then abg.obligee_id2nd " +
                "else 0 END topId" +
                ",IF ((abg.agent_id IS NOT NULL and abg.obligee_id1st!=0 AND abg.obligee_id2nd =0),agent.agent_name,abg.obligee1st) agentName1" +
                ",IF ((abg.agent_id IS NOT NULL and abg.obligee_id1st!=0 AND abg.obligee_id2nd =0),abg.obligee_id1st,null) obligeeId1" +
                ",IF ((abg.agent_id IS NOT NULL and obligee_id1st!=0 AND abg.obligee_id2nd !=0),agent.agent_name,NULL) agentName2" +
                ",IF ((abg.agent_id IS NOT NULL and obligee_id1st!=0 AND abg.obligee_id2nd !=0),abg.obligee_id2nd,NULL) obligeeId2" +
                ",count(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+",true,null)) as quantity" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+",o.order_price,0)) as amount" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+",goods.cost_price,0)) as amountCost" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+",o.order_price*agent.agent_discount,0)) as actualPaidAmount" +
                ",count(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +",true,null)) as returnQuantity" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +",goods.cost_price,0)) as returnAmountCost" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +",o.order_price*agent.agent_discount,0)) as actualReturnAmount" +
                ",count(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and (o.sys_tag="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" or o.sys_tag="+ OrderSysTagEnum.QXK_TAG_401.getValue() +"),true,null)) as stockoutQuantity" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and (o.sys_tag="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" or o.sys_tag="+ OrderSysTagEnum.QXK_TAG_401.getValue() +"),goods.cost_price,0)) as stockoutAmountCost" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and (o.sys_tag="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" or o.sys_tag="+ OrderSysTagEnum.QXK_TAG_401.getValue() +"),o.order_price*agent.agent_discount,0)) as actualStockoutAmount" +
                ",count(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag!="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +",true,null)) as actualQuantity" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag!="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +",goods.cost_price,0)) as actualAmountCost" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and o.sys_tag!="+ OrderSysTagEnum.YTHC_TAG_505.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +" and o.sys_tag!="+ OrderSysTagEnum.DHK_TAG_402.getValue() +",o.order_price*agent.agent_discount,0)) as actualAmount" +
                ",sum(if(agent.belong_type!="+GlobalConstant.PROFIT_BELONG_TYPE_PARTNER+" and agent.order_from="+OrderFromEnum.MEMBER.getValue()+",o.order_price,0)) onlinePaid" +
                ",sum(if(agent.profit_type="+GlobalConstant.PROFIT_TYPE_REBATE+",agent.agent_profit,0)) actRebate" +
                ",IF(abg.agent_id IS NOT NULL and abg.obligee1st IS NOT NULL AND abg.obligee2nd IS NULL,(select sum(oog.agent_profit) FROM omc_order_agent oog WHERE oog.agent_id=obligeeId1 and oog.profit_type = 3),NULL) bonusOne" +
                ",IF(abg.agent_id IS NOT NULL and abg.obligee1st IS NOT NULL AND abg.obligee2nd IS NOT NULL,(select sum(oog.agent_profit) FROM omc_order_agent oog WHERE oog.agent_id=obligeeId1 and oog.profit_type = 3),NULL) bonusTwo" +
                ",IF(abg.agent_id IS NOT NULL and abg.obligee1st IS NOT NULL AND abg.obligee2nd IS NOT NULL,(select sum(oog.agent_profit) FROM omc_order_agent oog WHERE oog.agent_id=obligeeId2 and oog.profit_type = 3),NULL) bonusTwo2" +
                ",(SELECT sum(oog.agent_profit) FROM omc_order_agent oog left join amc_agent_bonus_group aabg on aabg.id=oog.bonus_team_id WHERE (oog.profit_type=3 and agent.agent_id=aabg.agent_id) or (oog.profit_type=1 and agent.agent_id=oog.agent_id)) as rebate" +
                "");
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        //订单审核时间、订单取消时间、订单断货时间、订单退货时间
        q.and(StrUtil.isNotEmpty(startTime),
                i->i.and(j->j.ge("date_format(o.audit_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime))
                        .or(z->z.ge("date_format(o.canceled_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(o.canceled_time,'%Y-%m-%d')",endTime))
                        .or(l->l.ge("date_format(o.nosupplly_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(o.nosupplly_time,'%Y-%m-%d')",endTime))
                        .or(m->m.ge("date_format(afterSale.audited_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",endTime)));
        q.and(StrUtil.isNotEmpty(yearMonth),i->i.eq("date_format(o.audit_time,'%Y-%m')",yearMonth)
                .or().eq("date_format(o.canceled_time,'%Y-%m-%d')",yearMonth)
                .or().eq("date_format(o.nosupplly_time,'%Y-%m-%d')",yearMonth)
                .or().eq("date_format(afterSale.audited_time,'%Y-%m-%d')",yearMonth));
        q.eq(roleType!=null&&roleType==1,"agent.agent_pid",0);
        q.eq(roleType!=null&&roleType==1,"agent.kefu_id",agentId);
        q.eq(roleType!=null&&roleType==2,"agent.agent_pid",agentId);
        q.eq("o.order_type",OrderTypeEnum.SECKILL.getValue());
        q.groupBy("agent.agent_id");
        q.orderByAsc("topId");
        q.orderByDesc("isTop");
        return baseMapper.seckillSummary2(initMpPage,q);
    }

    @Override
    public IPage<FinanceSeckillSummaryDetail1Vo> seckillSummaryDetail1(Integer roleType, Long agentId,Integer status, String yearMonth, String startTime, String endTime, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.customer_id customerId,o.customer_name customerName" +
                ",IF (abg.agent_id IS NULL,agent.agent_name,null) agentName" +
                ",IF(agent.agent_id IS NOT NULL AND abg.agent_id IS NULL, 1, NULL) isTop" +
                ",case " +
                "when (abg.agent_id IS NULL) then agent.agent_id " +
                "when (abg.agent_id IS NOT NULL AND abg.obligee_id1st!=0 AND abg.obligee_id2nd =0) then abg.obligee_id1st " +
                "when (abg.agent_id IS NOT NULL AND abg.obligee_id1st!=0 AND abg.obligee_id2nd !=0) then abg.obligee_id2nd " +
                "else null END topId" +
                ",IF ((abg.agent_id IS NOT NULL and abg.obligee_id1st!=0 AND abg.obligee_id2nd =0),agent.agent_name,abg.obligee1st) agentName1" +
                ",IF ((abg.agent_id IS NOT NULL and abg.obligee_id1st!=0),abg.obligee_id1st,null) obligeeId1" +
                ",IF ((abg.agent_id IS NOT NULL and obligee_id1st!=0 AND abg.obligee_id2nd !=0),agent.agent_name,NULL) agentName2" +
                ",IF ((abg.agent_id IS NOT NULL and obligee_id1st!=0 AND abg.obligee_id2nd !=0),abg.obligee_id2nd,NULL) obligeeId2" +
                ",goods.quantity,goods.price amount,o.order_price*agent.agent_discount actualPaidAmount,goods.cost_price amountCost,goods.style_no,o.order_sn,pay.out_trade_no" +
                ",goods.size,goods.color,express.contact_phone,express.contact_user,o.created_time,o.audit_time" +
                ",sum(if(agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+", agent.agent_profit,0)) actRebate" +
                ",if(agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+",agent.agent_name,null) actRebateAgentName" +
                ",IF(abg.agent_id IS NOT NULL and abg.obligee_id1st!=0 AND abg.obligee_id2nd =0,(select sum(oog.agent_profit) FROM omc_order_agent oog WHERE oog.order_id=o.id and oog.agent_id=obligeeId1 and oog.profit_type = 3),NULL) bonusOne" +
                ",IF(abg.agent_id IS NOT NULL and abg.obligee_id1st!=0 AND abg.obligee_id2nd =0,abg.obligee1st,NULL) bonusOneAgentName" +
                ",IF(abg.agent_id IS NOT NULL and abg.obligee_id1st!=0 AND abg.obligee_id2nd !=0,(select sum(oog.agent_profit) FROM omc_order_agent oog WHERE oog.order_id=o.id and oog.agent_id=obligeeId1 and oog.profit_type = 3),NULL) bonusTwo" +
                ",IF(abg.agent_id IS NOT NULL and abg.obligee_id1st!=0 AND abg.obligee_id2nd !=0,abg.obligee1st,NULL) bonusTwoAgentName" +
                ",IF(abg.agent_id IS NOT NULL and abg.obligee_id1st!=0 AND abg.obligee_id2nd !=0,(select sum(oog.agent_profit) FROM omc_order_agent oog WHERE oog.order_id=o.id and oog.agent_id=obligeeId2 and oog.profit_type = 3),NULL) bonusTwo2" +
                ",IF(abg.agent_id IS NOT NULL and abg.obligee_id1st!=0 AND abg.obligee_id2nd !=0,abg.obligee2nd,NULL) bonusTwo2AgentName" +
                ",(SELECT sum(oog.agent_profit) FROM omc_order_agent oog left join amc_agent_bonus_group aabg on aabg.id=oog.bonus_team_id WHERE oog.profit_type=3 and agent.agent_id=aabg.agent_id) as rebate" +
                "");
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        q.eq(StrUtil.isNotEmpty(yearMonth),"date_format(o.audit_time,'%Y-%m')",yearMonth);
        q.eq(roleType!=null&&roleType==1,"agent.agent_pid",0);
        q.eq(roleType!=null&&roleType==1,"agent.kefu_id",agentId);
        q.eq(roleType!=null&&roleType==2,"agent.agent_pid",agentId);
        q.eq("o.order_type",OrderTypeEnum.SECKILL.getValue());
        q.eq(status!=null&&status==1,"o.sys_tag",OrderSysTagEnum.YTHC_TAG_505.getValue());
        q.and(status!=null&&status==2,i->i.eq("o.sys_tag",OrderSysTagEnum.DHK_TAG_402.getValue()).or().eq("o.sys_tag",OrderSysTagEnum.QXK_TAG_401.getValue()));
        q.and(status!=null&&status==3,i->i.eq("agent.profit_type",GlobalConstant.PROFIT_TYPE_BONUS).or().eq("agent.profit_type",GlobalConstant.PROFIT_TYPE_REBATE));
        q.orderByAsc("topId");
        q.orderByDesc("isTop");
        return baseMapper.seckillSummaryDetail1(initMpPage,q);
    }

    @Override
    public IPage<FinanceSeckillSummaryRebateDetailVo> seckilSummaryRebateDetail(Integer roleType, Long agentId, String yearMonth, String startTime, String endTime, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("agent.kefu_id customerId,agent.kefu_name customerName,agent.agent_name,agent.agent_id" +
                ",sum(agent.agent_profit) rebate");
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        q.eq(StrUtil.isNotEmpty(yearMonth),"date_format(o.audit_time,'%Y-%m')",yearMonth);
        q.eq(roleType!=null&&roleType==1,"agent.agent_pid",0);
        q.eq(roleType!=null&&roleType==1,"agent.kefu_id",agentId);
        q.and(roleType!=null&&roleType==2,i->i.eq("agent.agent_pid",agentId).or().eq("agent.agent_id",agentId));
        //计算分红和返利的，所以排除佣金的
        q.ne("agent.profit_type",GlobalConstant.PROFIT_TYPE_COMM);
        q.groupBy("agent.agent_id");
        q.eq("agent.order_type",OrderTypeEnum.SECKILL.getValue());
        return baseMapper.seckilSummaryRebateDetail(initMpPage,q);
    }

    @Override
    public IPage<FinancePromoteVo> financePromote(Long customerId, Long distributorId, String startTime, String endTime, String agentName, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        // TODO REPORT 操作人和操作时间待确定
        q.select("count(0) times,promote.agent_id,agent.agent_name,agent.customer_id,agent.customer_name" +
                ",SUBSTRING_INDEX(group_concat(parentBefore.agent_name order by promote.created_time desc),',',1) parentBefore" +
                ",SUBSTRING_INDEX(group_concat(parentAfter.agent_name order by promote.created_time desc),',',1) parentAfter" +
                ",max(promote.created_time) createdTime" +
                ",case SUBSTRING_INDEX(group_concat(promote.level_before order by promote.created_time desc),',',1) when "+ AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end levelBefore" +
                ",case SUBSTRING_INDEX(group_concat(promote.level_after order by promote.created_time desc),',',1) when "+ AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end levelAfter");
        q.eq(customerId!=null,"agent.customer_id",customerId);
        q.eq(distributorId!=null,"agent.dealer_id",distributorId);
        q.like(StrUtil.isNotEmpty(agentName),"agent.agent_name",agentName);
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(promote.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(promote.created_time,'%Y-%m-%d')",endTime);
        q.orderByDesc("promote.created_time");
        q.groupBy("promote.agent_id");
        IPage<FinancePromoteVo> page = baseMapper.agentPromoteList(initMpPage, q);
        return page;
    }

    @Override
    public IPage<FinancePromoteDetailVo> financePromoteDetail(Long agentId, String startTime, String endTime, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        // TODO REPORT 操作人和操作时间待确定
        q.select("promote.agent_id,agent.agent_name,agent.customer_id,agent.customer_name" +
                ",parentBefore.agent_name parentBefore,parentAfter.agent_name parentAfter,promote.created_time" +
                ",case promote.level_before when "+ AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end levelBefore" +
                ",case promote.level_after when "+ AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end levelAfter");
        q.eq("promote.agent_id",agentId);
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(promote.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(promote.created_time,'%Y-%m-%d')",endTime);
        q.orderByDesc("promote.created_time");
        return baseMapper.agentPromoteDetail(initMpPage, q);
    }

    @Override
    public IPage<FinanceAgentFeeVo> financeAgentFee(Integer roleType, Long agentId, String startTime, String endTime, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="'' as date";
        if (roleType!=null){
            select="date_format(agentLog.created_time,'%Y-%m-%d') date";
        }
        q.select(select+",agent.customer_id,agent.customer_name,'1'as roleType,count(if(agentLog.log_type="+AgentLogTypeEnum.SY.getValue()+",true,null)) probationNum" +
                ",count(if(agentLog.log_type="+AgentLogTypeEnum.SYGQ.getValue()+",true,null)) probationPastNum" +
                ",count(if(agentLog.log_type="+AgentLogTypeEnum.ZSGQ.getValue()+",true,null)) pastNum" +
                ",IFNULL(sum(if(agentLog.log_type="+AgentLogTypeEnum.ZHSK.getValue()+",agentLog.amount,0)),0) probationToOfficialAmount" +
                ",count(if(agentLog.log_type="+AgentLogTypeEnum.ZHSK.getValue()+",true,null)) probationToOfficialNum" +
                ",IFNULL(sum(if(agentLog.log_type="+AgentLogTypeEnum.ZSXF.getValue()+",agentLog.amount,0)),0) officialAmount" +
                ",count(if(agentLog.log_type="+AgentLogTypeEnum.ZSXF.getValue()+",true,null)) officialNum" +
                ",IFNULL(sum(if(agentLog.log_type="+AgentLogTypeEnum.TK.getValue()+",agentLog.amount,0)),0) refundAmount" +
                ",count(if(agentLog.log_type="+AgentLogTypeEnum.TK.getValue()+",true,null)) refundNum" +
                "");
        q.and(roleType!=null&&roleType==1,i->i.eq("agent.customer_id",agentId));
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(agentLog.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(agentLog.created_time,'%Y-%m-%d')",endTime);
        //roleType为空已客服分组统计
        q.groupBy(roleType==null,"agent.customer_id");
        q.groupBy(roleType!=null,"date");
        IPage<FinanceAgentFeeVo> page = baseMapper.financeAgentFee(initMpPage, q);
        if (page.getRecords().size()>0){
            //合计的数据放在数组第一个
            page.getRecords().add(0,financeAgentFeeSum(roleType,agentId,startTime,endTime));
        }
        return page;
    }
    public FinanceAgentFeeVo financeAgentFeeSum(Integer roleType, Long agentId, String startTime, String endTime) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="'' as roleType,'' as customerId";
        if (roleType!=null){
            select="'"+roleType+"' as roleType,agent.customer_id";
        }
        q.select(select+",'' as date,'合计' as customerName,count(if(agentLog.log_type="+AgentLogTypeEnum.SY.getValue()+",true,null)) probationNum" +
                ",count(if(agentLog.log_type="+AgentLogTypeEnum.SYGQ.getValue()+",true,null)) probationPastNum" +
                ",count(if(agentLog.log_type="+AgentLogTypeEnum.ZSGQ.getValue()+",true,null)) pastNum" +
                ",IFNULL(sum(if(agentLog.log_type="+AgentLogTypeEnum.ZHSK.getValue()+",agentLog.amount,0)),0) probationToOfficialAmount" +
                ",count(if(agentLog.log_type="+AgentLogTypeEnum.ZHSK.getValue()+",true,null)) probationToOfficialNum" +
                ",IFNULL(sum(if(agentLog.log_type="+AgentLogTypeEnum.ZSXF.getValue()+",agentLog.amount,0)),0) officialAmount" +
                ",count(if(agentLog.log_type="+AgentLogTypeEnum.ZSXF.getValue()+",true,null)) officialNum" +
                ",IFNULL(sum(if(agentLog.log_type="+AgentLogTypeEnum.TK.getValue()+",agentLog.amount,0)),0) refundAmount" +
                ",count(if(agentLog.log_type="+AgentLogTypeEnum.TK.getValue()+",true,null)) refundNum" +
                "");
        q.and(roleType!=null&&roleType==1,i->i.eq("agent.customer_id",agentId));
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(agentLog.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(agentLog.created_time,'%Y-%m-%d')",endTime);
        return baseMapper.financeAgentFeeSum(q);
    }
    @Override
    public IPage<FinanceAgentFeeDetailVo> financeAgentFeeDetail(Integer type,Integer roleType, Long agentId, String startTime, String endTime, String agentName,String mobile,String cardId, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String selectCommon="agent.customer_id,agent.customer_name,agentLog.agent_id,agentLog.agent_name,agentLog.mobile,agentLog.wechat_num,agentLog.id_card" +
                ",case agentLog.agent_level when "+ AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end agentLevel";
            q.select(selectCommon+",agentLog.effect_start,agentLog.effect_end,agentLog.log_type,agentLog.operator_name,agentLog.created_time,agentLog.amount");
            q.eq(type!=null,"agentLog.log_type",type);
        if (agentId==null){
            q.isNull(roleType!=null&&roleType==1,"agent.customer_id");
        }else{
            q.and(roleType!=null&&roleType==1,i->i.eq("agent.customer_id",agentId));
        }

        q.ge(StrUtil.isNotEmpty(startTime),"date_format(agentLog.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(agentLog.created_time,'%Y-%m-%d')",endTime);
        q.like(StrUtil.isNotEmpty(agentName),"agentLog.agent_name",agentName);
        q.like(StrUtil.isNotEmpty(mobile),"agentLog.mobile",mobile);
        q.like(StrUtil.isNotEmpty(cardId),"agentLog.id_card",cardId);
        IPage<FinanceAgentFeeDetailVo> page = baseMapper.financeAgentLog(initMpPage, q);
        return page;
    }

    @Override
    public IPage<FinanceCustomerPerformanceVo> financeCustomerPerformance(Integer orderType,String actName,  String startTime, String endTime, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("agent.order_type" +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as amount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,0)) as actualPaidAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.cost_price,0)) as amountCost" +
                // 退货
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue() +",true,null)) as returnQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue() +",o.order_price,0)) as returnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue() +",agent.need_receive,0)) as actualReturnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue() +",goods.cost_price,0)) as returnAmountCost" +
                // 取消和断货
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue() +" or log.log_type="+OrderLogTypeEnum.CANCEL.getValue() +",true,null)) as stockoutQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue() +" or log.log_type="+OrderLogTypeEnum.CANCEL.getValue() +",o.order_price,0)) as stockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue() +" or log.log_type="+OrderLogTypeEnum.CANCEL.getValue() +",agent.need_receive,0)) as actualStockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue() +" or log.log_type="+OrderLogTypeEnum.CANCEL.getValue() +",goods.cost_price,0)) as stockoutAmountCost" +

                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,-o.order_price)) as actualAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.cost_price,-goods.cost_price)) as actualAmountCost" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,-agent.need_receive)) as actualSaleAmount" +
                ",sum(if(agent.profit_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as rebate" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+" and agent.order_from="+ OrderFromEnum.MEMBER.getValue() +",o.order_price,0)) as onlinePaid" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+" and agent.order_from="+ OrderFromEnum.AGENT.getValue() +",o.order_price,0)) as offlinePaid" +
                "");
        q.eq("agent.role_type",1);
        q.groupBy("agent.order_type");
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //只筛选审核完成的订单
        q.isNotNull("log.created_time");
        //订单审核时间、订单取消时间、订单断货时间、订单退货时间
        q.and(StrUtil.isNotEmpty(startTime),
                i->i.and(j->j.ge("date_format(log.created_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime)));
        q.eq(orderType!=null,"agent.order_type",orderType);
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        IPage page = baseMapper.financeCustomerPerformance(initMpPage, q);
        return page;
    }

    @Override
    public IPage<FinanceCustomerPerformanceDetailVo> financeCustomerPerformanceDetail(Integer orderType, String actName,Integer roleType, Long agentId,Long agentPid,Integer pRole, String userName, String startTime, String endTime, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="";
        if (roleType==null){
            select="o.customer_id customerId,o.customer_name customerName,agent.agent_id agentId,agent.agent_name agentName,1 as roleType,'客服' as userRole";
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
        }else{
            select="o.customer_id customerId,o.customer_name customerName,agent.agent_id agentId,agent.agent_name,2 as roleType" +
                    ",case agent.agent_level when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                    "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                    "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                    "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                    "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                    "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole";
            //如果是角色类型是客服，就筛选 客服自己下单的和客服名下代理商的数据
            q.and(roleType==1,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",1).eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN))
                    .or(z->z.eq("agent.role_type",2).eq("agent.kefu_id",agentId).eq("agent.agent_pid",0)));
            //如果是角色类型是代理商，就筛选 代理商自己的和代理商名下代理商的数据
            q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN).eq("agent.role_type",2).eq(pRole!=null&&pRole==1,"agent.agent_pid",0).eq(pRole!=null&&pRole==2,"agent.agent_pid",agentPid))
                    .or(z->z.eq("agent.agent_pid",agentId).eq("agent.role_type",2).and(agentPid!=null,x->x.eq("o.customer_id",agentPid).or().eq("o.distributor_id",agentPid).or().eq("o.level1st",agentPid).or().eq("o.level2nd",agentPid).or().eq("o.level3rd",agentPid).or().eq("o.level4th",agentPid))));
        }
        q.select(select+",agent.order_type" +
                ",if(agent.agent_id="+agentId+" and agent.role_type="+roleType+",1,0) as isOwn" +
                ",if(agent.agent_id="+agentId+" and agent.role_type="+roleType+" ,if("+pRole+">0,if("+pRole+"=1,0,"+agentPid+"),null),if("+roleType+"=1,0,"+agentId+")) as agentPid" +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as amount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,0)) as actualPaidAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.cost_price,0)) as amountCost" +
                //  退货
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue() +",true,null)) as returnQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue() +",o.order_price,0)) as returnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue() +",agent.need_receive,0)) as actualReturnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue() +",goods.cost_price,0)) as returnAmountCost" +
                // 取消和断货
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue() +" or log.log_type="+OrderLogTypeEnum.CANCEL.getValue() +",true,null)) as stockoutQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue() +" or log.log_type="+OrderLogTypeEnum.CANCEL.getValue() +",o.order_price,0)) as stockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue() +" or log.log_type="+OrderLogTypeEnum.CANCEL.getValue() +",agent.need_receive,0)) as actualStockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue() +" or log.log_type="+OrderLogTypeEnum.CANCEL.getValue() +",goods.cost_price,0)) as stockoutAmountCost" +

                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,-o.order_price)) as actualAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.cost_price,-goods.cost_price)) as actualAmountCost" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,-agent.need_receive)) as actualSaleAmount" +
                ",sum(if(agent.profit_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as rebate" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+" and agent.order_from="+ OrderFromEnum.MEMBER.getValue() +",o.order_price,0)) as onlinePaid" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+" and agent.order_from="+ OrderFromEnum.AGENT.getValue() +",o.order_price,0)) as offlinePaid" +
                "");
        q.like(StrUtil.isNotEmpty(userName),"agent.agent_name",userName);
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //只筛选审核完成的订单
        q.isNotNull("log.created_time");
        //订单审核时间、订单取消时间、订单断货时间、订单退货时间
        q.and(StrUtil.isNotEmpty(startTime),
                i->i.and(j->j.ge("date_format(log.created_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime)));
        q.eq(orderType!=null,"agent.order_type",orderType);
        q.groupBy("agent.agent_id","agent.role_type","agent.order_type");
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        IPage<FinanceCustomerPerformanceDetailVo> pageData = baseMapper.financeCustomerPerformanceDetail(initMpPage, q);
        if (pageData.getRecords().size()>0){
            //合计的数据放在数组第一个
            pageData.getRecords().add(0,financeCustomerPerformanceDetailSum(orderType,actName,roleType,agentId,agentPid,pRole,userName,startTime,endTime));
        }
        return pageData;
    }


    public FinanceCustomerPerformanceDetailVo financeCustomerPerformanceDetailSum(Integer orderType,String actName, Integer roleType, Long agentId,Long agentPid,Integer pRole, String userName, String startTime, String endTime) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="";
        if (roleType==null){
            select="'合计'as agentName";
            //如果角色类型为空，角色类型为客服的订单
            q.eq("agent.role_type",1);
        }else{
            if (roleType==1){
                select="agent.agent_id agentId,agent.role_type roleType" +
                        ",'合计' agentName" +
                        "";
                //点击客服树的代理商Id和角色类型
                q.eq("agent.role_type",roleType);
                q.eq("agent.agent_id",agentId);
            }
            if (roleType==2){
                select="agent.agent_id,agent.role_type roleType" +
                        ",'合计' agentName" +
                        "";
                q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",2).eq(pRole!=null&&pRole==1,"agent.agent_pid",0).eq(pRole!=null&&pRole==2,"agent.agent_pid",agentPid)));
                if (pRole!=null){
                    select=select+",agent.agent_pid";
                }
            }
        }
        q.select(select+
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as amount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,0)) as actualPaidAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.cost_price,0)) as amountCost" +
                //  退货
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue() +",true,null)) as returnQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue() +",o.order_price,0)) as returnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue() +",agent.need_receive,0)) as actualReturnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue() +",goods.cost_price,0)) as returnAmountCost" +
                // 取消和断货
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue() +" or log.log_type="+OrderLogTypeEnum.CANCEL.getValue() +",true,null)) as stockoutQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue() +" or log.log_type="+OrderLogTypeEnum.CANCEL.getValue() +",o.order_price,0)) as stockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue() +" or log.log_type="+OrderLogTypeEnum.CANCEL.getValue() +",agent.need_receive,0)) as actualStockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue() +" or log.log_type="+OrderLogTypeEnum.CANCEL.getValue() +",goods.cost_price,0)) as stockoutAmountCost" +

                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,-o.order_price)) as actualAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.cost_price,-goods.cost_price)) as actualAmountCost" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,-agent.need_receive)) as actualSaleAmount" +
                ",sum(if(agent.profit_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as rebate" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+" and agent.order_from="+ OrderFromEnum.MEMBER.getValue() +",o.order_price,0)) as onlinePaid" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+" and agent.order_from="+ OrderFromEnum.AGENT.getValue() +",o.order_price,0)) as offlinePaid" +
                "");
        q.like(StrUtil.isNotEmpty(userName),"agent.agent_name",userName);
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);
        //只筛选审核完成的订单
        q.isNotNull("log.created_time");
        //订单审核时间、订单取消时间、订单断货时间、订单退货时间
        q.and(StrUtil.isNotEmpty(startTime),
                i->i.and(j->j.ge("date_format(log.created_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime)));
        q.eq(orderType!=null,"agent.order_type",orderType);
        return baseMapper.financeCustomerPerformanceDetailSum( q);
    }
}
